/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Facultad;
import com.javabeans.Carrera;

/**
 * @author Administrador
 */
public class AdminCarrera implements CarreraManager{
    private Connection c;

    public AdminCarrera(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdCarrera() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDCARRERA)+1,1) AS ID FROM CARRERA";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getIdCarrera() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveCarrera(Carrera ca)throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO CARRERA VALUES("+ca.getIdCarrera();
            query += ", '"+ca.getNombre().toUpperCase();
            query += "', '"+ca.getSigla().toUpperCase();
            query += "', '"+ca.getFecha()+"',"+ca.isActivo()+","+ca.getIdFacultad()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> saveCarrera(Carrera c) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Carrera> getCarrerasAll() throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        try{
            carreras = this.getCarrerasGeneric(0);
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrerasAll() e = "+e.getMessage());
        }
        return carreras;
    }
    
    @Override
    public List<Carrera> getCarrerasAllByFacul(int idFacultad) throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        try{
            carreras = this.getCarrerasGeneric(idFacultad);
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrerasAllByFacul() e = "+e.getMessage());
        }
        return carreras;
    }
    
    public List<Carrera> getCarrerasGeneric(int idFacultad) throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDCARRERA";
            query += ", C.NOMBRE, C.SIGLA, DATE_FORMAT(C.FECHA,'%d/%m/%Y') FECHA";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += " FROM CARRERA C";
            query += " INNER JOIN FACULTAD F ON C.IDFACULTAD=F.IDFACULTAD";
            query += " WHERE C.ACTIVO=1";
            if(idFacultad != 0){
                query += " AND C.IDFACULTAD="+idFacultad;
                query += " ORDER BY C.NOMBRE";
            }else{
                query += " ORDER BY F.NOMBRE, C.NOMBRE";
            }            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));                
                Carrera ca = new Carrera();
                ca.setIdCarrera(rs.getInt("C.IDCARRERA"));
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setFecha(rs.getString("FECHA"));
                ca.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                ca.setFacultad(f);
                carreras.add(ca);
            }
        }catch(SQLException e){
            throw new DataException(" -> getCarrerasGeneric(int idFacultad) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return carreras;
    }
    
    @Override
    public List<Carrera> getCarrerasInactivos() throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDCARRERA";
            query += ", C.NOMBRE, C.SIGLA, DATE_FORMAT(C.FECHA,'%d/%m/%Y') FECHA";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += " FROM CARRERA C";
            query += " INNER JOIN FACULTAD F ON C.IDFACULTAD=F.IDFACULTAD";
            query += " WHERE C.ACTIVO=0";
            query += " ORDER BY F.NOMBRE, C.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                
                Carrera ca = new Carrera();
                ca.setIdCarrera(rs.getInt("C.IDCARRERA"));
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setFecha(rs.getString("FECHA"));
                ca.setFecha(rs.getString("F.IDFACULTAD"));
                ca.setFacultad(f);
                carreras.add(ca);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrerasInactivos() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return carreras;
    }
    
    @Override
    public Carrera getCarreraBasic(int idCarrera) throws DataException{
        Carrera ca = new Carrera();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCARRERA";
            query += ", NOMBRE, SIGLA, DATE_FORMAT(FECHA,'%d/%m/%Y') FECHA, IDFACULTAD, ACTIVO";
            query += " FROM CARRERA";
            query += " WHERE IDCARRERA="+idCarrera;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                ca.setIdCarrera(rs.getInt("IDCARRERA"));
                ca.setNombre(rs.getString("NOMBRE"));
                ca.setSigla(rs.getString("SIGLA"));
                ca.setFecha(rs.getString("FECHA"));
                ca.setActivo(rs.getBoolean("ACTIVO"));
                ca.setIdFacultad(rs.getInt("IDFACULTAD"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarreraBasic() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return ca;
    }
    
    @Override
    public Carrera getCarrera(int idCarrera) throws DataException{
        Carrera ca = new Carrera();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDCARRERA";
            query += ", C.NOMBRE, C.SIGLA, DATE_FORMAT(C.FECHA,'%d/%m/%Y') FECHA";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += " FROM CARRERA C";
            query += " INNER JOIN FACULTAD F ON C.IDFACULTAD=F.IDFACULTAD";
            query += " WHERE C.IDCARRERA="+idCarrera;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                
                ca.setIdCarrera(rs.getInt("C.IDCARRERA"));
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setFecha(rs.getString("FECHA"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrera() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return ca;
    }
    
    @Override
    public void updateCarrera(Carrera ca) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CARRERA SET NOMBRE = '"+ca.getNombre().toUpperCase();
            query+= "', SIGLA = '"+ca.getSigla().toUpperCase();
            query+= "', FECHA = '"+ca.getFecha();
            query+= "' WHERE IDCARRERA="+ca.getIdCarrera();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> updateCarrera(Carrera ca) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void deleteCarrera(int idCarrera) throws DataException {
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "DELETE FROM MATERIA WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();

            query = "DELETE FROM CARRERA WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> deleteCarrera(int idCarrera) query ====== "+query+" e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseCarreraForDelete(int idCarrera) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDCARRERA";
        query += " FROM CARRERA_ESTUDIANTE";
        query += " WHERE IDCARRERA="+idCarrera+" LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                res = true;
            }else{
                query = "SELECT IDINSCRITO";
                query += " FROM INSCRITO";
                query += " WHERE IDCARRERA = "+idCarrera+" LIMIT 1";
                preSt = c.prepareStatement(query);
                rs = preSt.executeQuery();
                if(rs.next())
                    res = true;
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> isUseCarreraForDelete(int idCarrera) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void darBaja(int idCarrera) throws DataException{
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "UPDATE MATERIA SET ACTIVO = 0 WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE CARRERA SET ACTIVO = 0 WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> darBaja(int idCarrera) QUERY ========= "+query+" ========= e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idCarrera) throws DataException{
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "UPDATE MATERIA SET ACTIVO = 1 WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE CARRERA SET ACTIVO = 1 WHERE IDCARRERA="+idCarrera;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> darAlta(int idCarrera) query ===== "+query+" ==== e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Carrera> getCarrerasPorEst(int codigoSis) throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "SELECT C.IDCARRERA";
            query += ", C.NOMBRE";
            query += ", C.SIGLA";
            query += ", C.IDFACULTAD";
            query += ", F.NOMBRE, F.SIGLA";
            query += " FROM CARRERA_ESTUDIANTE CE";
            query += " INNER JOIN ESTUDIANTE E ON CE.IDESTUDIANTE=E.IDESTUDIANTE";
            query +=" INNER JOIN (CARRERA C INNER JOIN FACULTAD F ON C.IDFACULTAD=F.IDFACULTAD)";
            query +=" ON CE.IDCARRERA=C.IDCARRERA";
            query += " WHERE E.IDESTUDIANTE="+codigoSis;
            query += " AND CE.ESTADO=1";
            query += " ORDER BY C.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(rs.getInt("C.IDCARRERA"));
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                Facultad f = new Facultad();
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                ca.setFacultad(f);
                carreras.add(ca);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrerasPorEst(int codigoSis) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return carreras;
    }
    
    @Override
    public List<Carrera> getCarrerasPorEstBasic(int codigoSis) throws DataException{
        List<Carrera> carreras = new ArrayList<Carrera>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "SELECT C.IDCARRERA";
            query += ", C.NOMBRE";
            query += ", C.SIGLA";
            query += ", C.IDFACULTAD";
            query += " FROM CARRERA_ESTUDIANTE CE";
            query +=" INNER JOIN CARRERA C ON CE.IDCARRERA=C.IDCARRERA";
            query += " WHERE CE.IDESTUDIANTE="+codigoSis;
            query += " AND CE.ESTADO=1";
            query += " ORDER BY C.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(rs.getInt("C.IDCARRERA"));
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                carreras.add(ca);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCarrera -> getCarrerasPorEstBasic(int codigoSis) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return carreras;
    }
}
